<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017/10/20
 * Time: 14:48
 */

namespace app\common\service;


use app\common\access\MyAccess;
use app\common\access\MyService;
use think\Db;

class Rite extends MyService
{
    //读取督导列表
    function getList($page=1,$rows=20,$studentno='%',$studentname='%',$classno='%',$school='',$status='',$score='',$sort='studentno',$order='',$teacherno='%')
    {
        $result = ['total' => 0, 'rows' => []];
        $condition = null;
        if ($studentno != '%') $condition['students.studentno'] = array('like', $studentno);
        if ($studentname != '%') $condition['students.name'] = array('like', $studentname);
        if ($classno != '%') $condition['students.classno'] = array('like', $classno);
        if ($school != '') $condition['classes.school'] = $school;
        if ($status != '') $condition['students.status'] = $status;
        if ($score != '') {
            if ($score == 0) $condition['rite.score'] = array('exp', 'is null');
            if ($score == 1) $condition['rite.score'] = '合格';
            if ($score == 2) $condition['rite.score'] = '不合格';
            if ($score == 3) $condition['rite.score'] = array('exp', 'is not null');
        }
        if($teacherno!='%')
            $condition['rite.teacherno']=$teacherno;
        $data=$this->query->table('students')
            ->join('classes','classes.classno=students.classno')
            ->join('schools ',' schools.school=classes.school')
            ->join('statusoptions','statusoptions.name=students.status')
            ->join('sexcode','students.sex=sexcode.code')
            ->join('rite','students.studentno=rite.studentno','LEFT')
            ->join('teachers','teachers.teacherno=rite.teacherno','LEFT')
            ->page($page,$rows)
            ->field("students.studentno,rtrim(students.name) studentname,rtrim(sexcode.name) sexname,students.classno,rtrim(classes.classname) classname,
            rtrim(schools.name) schoolname,classes.school,rtrim(statusoptions.value) statusname,isnull(rite.score,'无') score,rtrim(teachers.name) teachername
            ,  convert(varchar(100),date,120) date")
            ->where($condition)->order($sort.' '.$order)->select();
        $count= $this->query->table('students')
            ->join('classes','classes.classno=students.classno')
            ->join('rite','students.studentno=rite.studentno','LEFT')
            ->where($condition)->count();
        if(is_array($data)&&count($data)>0)
            $result=array('total'=>$count,'rows'=>$data);
        return $result;
    }


    //更新信息
    public function  update($postData){
        $updateRow=0;
        $deleteRow=0;
        $insertRow=0;
        $errorRow=0;
        $info="";
        $status=1;
        //更新部分
        //开始事务

        try {
        if (isset($postData["updated"])) {
            $updated = $postData["updated"];
            $listUpdated = json_decode($updated);
            foreach ($listUpdated as $one) {
                $condition = null;
                $data = null;
                if(MyAccess::checkStudentSchool($one->studentno)){
                    $score=$postData["score"];
                    $updateRow+=self::addScore($one->studentno,$score);
                }
                else{
                    $info.=$one->studentno.'不是本学院学生，无法更改成绩。</br>';
                    $errorRow++;
                    $status=0;
                }
            }
        }
         }
         catch(\Exception $e){
             throw $e;
         }
        if($updateRow+$deleteRow+$insertRow+$errorRow==0){
            $status=0;
            $info="没有数据更新";
        }
        else {
            if ($updateRow > 0) $info .= $updateRow . '条更新！</br>';
            if ($deleteRow > 0) $info .= $deleteRow . '条删除！</br>';
            if ($insertRow > 0) $info .= $insertRow . '条添加！</br>';
        }
        $result=array('info'=>$info,'status'=>$status);
        return $result;
    }

    private static  function addScore($studentno,$score)
    {
        $teacherno=session('S_TEACHERNO');
        $rows=0;
        $bind=array('STUDENTNO'=>$studentno,'SCORE'=>$score,'STUDENTNO2'=>$studentno,'SCORE2'=>$score,'TEACHERNO'=>$teacherno);

        $sql="IF  EXISTS (SELECT * FROM RITE WHERE STUDENTNO=:STUDENTNO AND SCORE!=:SCORE)
            UPDATE RITE
            SET SCORE=:SCORE2,DATE=GETDATE(),TEACHERNO=:TEACHERNO
            WHERE STUDENTNO=:STUDENTNO2";
        $rows+=Db::execute($sql,$bind);

        $bind=array('STUDENTNO'=>$studentno,'STUDENTNO2'=>$studentno,'SCORE2'=>$score,'TEACHERNO'=>$teacherno);
        $sql="IF NOT EXISTS (SELECT * FROM RITE WHERE STUDENTNO=:STUDENTNO)
            INSERT INTO RITE(STUDENTNO,SCORE,TEACHERNO)
            VALUES(:STUDENTNO2,:SCORE2,:TEACHERNO)";
        $rows+=Db::execute($sql,$bind);
        return $rows;
    }



}